package db.tools;

import hirondelle.date4j.DateTime;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

/**
 * Loads XLS spreadsheet into the database.
 * Currently only understands one particular format
 * @author alexey
 *
 */
public class LoadXLS {

	private Connection conn;
	private int numericalIndicatorId;
	private int nominalIndicatorId;
	
	/**
	 * For each column index this map gives the timestamp
	 */
	private Map<Integer, DateTime> timeHeader = new HashMap<Integer, DateTime>();

	/**
	 * Indicates whether we have already read the header
	 */
	private boolean haveHeader = false;
	
	LoadXLS(Connection conn, String numericalIndicator, String nominalIndicator) throws SQLException {
	    this.conn = conn;
	    // Get numerical indicator id if exists
	    PreparedStatement psNumerical = conn.prepareStatement("SELECT id FROM numerical WHERE name=?");
	    psNumerical.setString(1, numericalIndicator);
	    ResultSet rsNumerical = psNumerical.executeQuery();
	    if (rsNumerical.next()) {
	    	numericalIndicatorId = rsNumerical.getInt(1);
	    } else {
	    	// Insert new numerical indicator
	    	PreparedStatement psNewNumerical = conn.prepareStatement("INSERT INTO numerical (name) VALUES (?)");
	    	psNewNumerical.setString(1, numericalIndicator);
	    	psNewNumerical.executeUpdate();
	    	ResultSet rsNewNumerical = psNewNumerical.getGeneratedKeys();
	    	rsNewNumerical.next();
	    	numericalIndicatorId = rsNewNumerical.getInt(1);
	    	rsNewNumerical.close();
	    	psNewNumerical.close();
	    }
	    rsNumerical.close();
	    psNumerical.close();
	    PreparedStatement psNominal = conn.prepareStatement("SELECT id FROM nominal WHERE name=?");
	    psNominal.setString(1, nominalIndicator);
	    ResultSet rsNominal = psNominal.executeQuery();
	    if (rsNominal.next()) {
	    	nominalIndicatorId = rsNominal.getInt(1);
	    } else {
	    	// Insert new nominal indicator
	    	PreparedStatement psNewNominal = conn.prepareStatement("INSERT INTO nominal (name) VALUES (?)");
	    	psNewNominal.setString(1, nominalIndicator);
	    	psNewNominal.executeUpdate();
	    	ResultSet rsNewNominal = psNewNominal.getGeneratedKeys();
	    	rsNewNominal.next();
	    	nominalIndicatorId = rsNewNominal.getInt(1);
	    	rsNewNominal.close();
	    	psNewNominal.close();
	    }
	    rsNominal.close();
	    psNominal.close();
	}
	
	
	/**
	 * @param args Parameters specify the file name to load, the numerical indicator to populate
	 * and the nominal indicator used
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws FileNotFoundException, IOException, ClassNotFoundException, SQLException {
		if (args.length < 3) {
			System.out.println("Arguments: <xls file> <name of numerical indicator> <name of nominal indicator>");
			return;
		}
		long startTime = System.currentTimeMillis();
		File xlsFile = new File(args[0]);
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(xlsFile));
		Class.forName("org.h2.Driver");
		String numericalIndicator = args[1];
		String nominalIndicator = args[2];
		Connection conn = DriverManager.getConnection("jdbc:h2:resources/bubble_data", "sa", "");
		// How many knots before loading
		Statement st = conn.createStatement();
		ResultSet startCountRs = st.executeQuery("SELECT COUNT(1) FROM knot");
		startCountRs.next();
		System.out.println("Knots before execution: " + startCountRs.getInt(1));
		startCountRs.close();
	    LoadXLS loadXls = new LoadXLS(conn, numericalIndicator, nominalIndicator);
	    loadXls.processWorkbook(workbook);
		ResultSet endCountRs = st.executeQuery("SELECT COUNT(1) FROM knot");
		endCountRs.next();
		System.out.println("Knots after execution: " + endCountRs.getInt(1));
		endCountRs.close();
	    st.close();
	    conn.close();
	    long endTime = System.currentTimeMillis();
	    System.out.println("Execution time: " + (endTime - startTime) / 1000 + " seconds");
	}
	
	private void processWorkbook(HSSFWorkbook workbook) throws SQLException {
		// Only process the first sheet in the workbook
		processSheet(workbook.getSheetAt(0));
	}
	
	private void processSheet(HSSFSheet sheet) throws SQLException {
		haveHeader = false;
		timeHeader.clear();
		for (int i=0;i<sheet.getLastRowNum();i++) {
			processRow(sheet.getRow(i));
		}
	}
	
	private void processRow(HSSFRow row) throws SQLException {
		if (!haveHeader) {
			if (tryProcessHeader(row)) {
				haveHeader = true;
			} else {
				timeHeader.clear();
			}
		} else {
			// Ids of nominal values that need to be attached to every knot
			List<Integer> nominalValues = new LinkedList<Integer>();
			// Map of knot ids and their respective times
			Map<Integer, DateTime> knots = new LinkedHashMap<Integer, DateTime>();
			for (int i=0;i<row.getLastCellNum();i++) {
				HSSFCell cell = row.getCell(i);
				if (cell == null) {
					continue;
				}
				int cellType = cell.getCellType();
				if (timeHeader.containsKey(i)) {
					DateTime timestamp = timeHeader.get(i);
					// We should have a numerical indicator value here
					if (cellType == Cell.CELL_TYPE_NUMERIC) {
						double value = cell.getNumericCellValue();
						// Add knot_numerical
						knots.put(addKnotNumerical(timestamp, value, nominalValues), timestamp);
					}
				} else {
					if (cellType == Cell.CELL_TYPE_STRING) {
						String value = cell.getStringCellValue();
						// Add knot_nominal, if not exist already
						nominalValues.add(addKnotNominal(value, knots.keySet()));
					}
				}
			}
			// Remove knots that have the same nominal values and timestamps
			deduplicate(knots);
		}
	}
	
	/**
	 * Finds knots with the same sets of nominal values and removes them from the 
	 * database, effectively. But their numberical values have to be copied
	 * into the new entries
	 * @param knots
	 * @throws SQLException 
	 */
	void deduplicate(Map<Integer, DateTime> knots) throws SQLException {
		PreparedStatement countNominalPs = conn.prepareStatement("SELECT COUNT(1) FROM knot_nominal WHERE knot_id=?");
		PreparedStatement findNominalPs = conn.prepareStatement(
		"SELECT kn.knot_id, COUNT(1) numValues FROM knot_nominal kn, knot k "+
		" WHERE k.id=kn.knot_id AND kn.knot_id<>? AND kn.nominal_value_id IN "+
		   "(SELECT kn1.nominal_value_id FROM knot_nominal kn1 WHERE kn1.knot_id=?) "+
		" AND k.t=? GROUP BY kn.knot_id");
		PreparedStatement updateNumericalPs = conn.prepareStatement(
		"UPDATE knot_numerical kn SET knot_id=? WHERE kn.knot_id = ? AND kn.numerical_id NOT IN "+
		   "(SELECT kn1.numerical_id FROM knot_numerical kn1 WHERE kn1.knot_id=?)");
		List<Integer> toDelete = new LinkedList<Integer>();
		for (Map.Entry<Integer, DateTime> knotEntry: knots.entrySet()) {
			countNominalPs.setInt(1, knotEntry.getKey());
			ResultSet countRs = countNominalPs.executeQuery();
			countRs.next();
			int count = countRs.getInt(1);
			countRs.close();
			findNominalPs.setInt(1, knotEntry.getKey());
			findNominalPs.setInt(2, knotEntry.getKey());
			findNominalPs.setTimestamp(3, new Timestamp(knotEntry.getValue().getMilliseconds(TimeZone.getTimeZone("UTC"))));
			ResultSet findNominalRs = findNominalPs.executeQuery();
			while (findNominalRs.next()) {
				int knotId = findNominalRs.getInt(1);
				int thisCount = findNominalRs.getInt(2);
				if (thisCount == count) {
					updateNumericalPs.setInt(1, knotEntry.getKey());
					updateNumericalPs.setInt(2, knotId);
					updateNumericalPs.setInt(3, knotEntry.getKey());
					updateNumericalPs.execute();
					toDelete.add(knotId);
				}
			}
			findNominalRs.close();
		}
		countNominalPs.close();
		findNominalPs.close();
		updateNumericalPs.close();
		PreparedStatement deletePs = conn.prepareStatement("DELETE FROM knot WHERE id=?");
		for (int knotId: toDelete) {
			// Delete this knot and all the elements (performed by CASCADE constraints)
			deletePs.setInt(1, knotId);
			deletePs.executeUpdate();
		}
		deletePs.close();
	}

	/**
	 * @param nominalIndicatorId
	 * @param value
	 * @param knots Collection of knots Ids to retrofit with this nominal value
	 * @return Id of the nominal value record
	 * @throws SQLException 
	 */
	int addKnotNominal(String value, Collection<Integer> knots) throws SQLException {
		// Check if this value already exists and if not, create it
		int nominalValueId;
		PreparedStatement findPs = conn.prepareStatement("SELECT id FROM nominal_value WHERE nominal_id=? AND value=?");
		findPs.setInt(1, nominalIndicatorId);
		findPs.setString(2, value);
		ResultSet findRs = findPs.executeQuery();
		if (findRs.next()) {
			nominalValueId = findRs.getInt(1);
		} else {
			// Does not exist, so we insert one
			PreparedStatement newPs = conn.prepareStatement("INSERT INTO nominal_value (nominal_id,value) VALUES (?,?)");
			newPs.setInt(1, nominalIndicatorId);
			newPs.setString(2, value);
			newPs.executeUpdate();
			ResultSet newRs = newPs.getGeneratedKeys();
			newRs.next();
			nominalValueId = newRs.getInt(1);
			newRs.close();
			newPs.close();
		}
		findRs.close();
		findPs.close();
		// Retrofit knots
		PreparedStatement knotPs = conn.prepareStatement("INSERT INTO knot_nominal (knot_id,nominal_value_id) VALUES (?,?)");
		for (int knotId: knots) {
			knotPs.setInt(1, knotId);
			knotPs.setInt(2, nominalValueId);
			knotPs.executeUpdate();
		}
		knotPs.close();
		return nominalValueId;
	}

	/**
	 * Adds knots and attaches numerical values to them. If necessary, retrofit the knot
	 * with the nominal values created earlier.
	 * @param timestamp
	 * @param value
	 * @param nominalValues Collection of nominal value Ids that also need to be
	 * attached to the newly created knot
	 * @return Id of the knot created
	 * @throws SQLException 
	 */
	int addKnotNumerical(DateTime timestamp, double value, Collection<Integer> nominalValues) throws SQLException {
		int knotId;
		PreparedStatement newKnotPs = conn.prepareStatement("INSERT INTO knot (t) VALUES (?)");
		newKnotPs.setTimestamp(1, new Timestamp(timestamp.getMilliseconds(TimeZone.getTimeZone("UTC"))));
		newKnotPs.executeUpdate();
		ResultSet newKnotRs = newKnotPs.getGeneratedKeys();
		newKnotRs.next();
		knotId = newKnotRs.getInt(1);
		newKnotRs.close();
		newKnotPs.close();
		// Attach numerical value
		PreparedStatement numericalPs = conn.prepareStatement("INSERT INTO knot_numerical (knot_id,numerical_id,value) VALUES (?,?,?)");
		numericalPs.setInt(1, knotId);
		numericalPs.setInt(2, numericalIndicatorId);
		numericalPs.setDouble(3, value);
		numericalPs.executeUpdate();
		numericalPs.close();
		// Attach nominal values
		PreparedStatement nominalPs = conn.prepareStatement("INSERT INTO knot_nominal (knot_id,nominal_value_id) VALUES (?,?)");
		for (int valueId: nominalValues) {
			nominalPs.setInt(1, knotId);
			nominalPs.setInt(2, valueId);
			nominalPs.executeUpdate();
		}
		nominalPs.close();
		return knotId;
	}

	private boolean tryProcessHeader(HSSFRow row) {
		DateTime lastTimestamp = null;
		for (int i=0;i<row.getLastCellNum();i++) {
			HSSFCell cell = row.getCell(i);
			if (cell == null) {
				continue;
			}
			int cellType = cell.getCellType();
			if (cellType == Cell.CELL_TYPE_NUMERIC) {
				int year = new Double(cell.getNumericCellValue()).intValue();
				if (year < 1 || year > 9999) {
					return false;
				}
				DateTime timestamp = DateTime.forDateOnly(year, 12, 31);
				// We expect the years to increase
				if (lastTimestamp == null || timestamp.compareTo(lastTimestamp) == 1) {
					lastTimestamp = timestamp;
				} else {
					return false;
				}
				timeHeader.put(i, timestamp);
			}
		}
		// Only report success if there is at least one year
		return lastTimestamp != null;
	}

}
